var mysql = require('mysql');
var config = require('../config/sqlConfig.js')

//建立数据库连接池
var pool = mysql.createPool({
    host: config.database.HOST,
    user: config.database.USERNAME,
    password: config.database.PASSWORD,
    database: config.database.DATABASE,
    multipleStatements: true, // 允许同时查询多条语句
});

let query = function(sql, values) {
    return new Promise((resolve, reject)=>{
        pool.getConnection(function (err,connection) {
            if(err){
                reject(err);
            }else{
                connection.query(sql,values,(err,rows)=>{
                    if(err){
                        reject(err);
                    }else{
                        resolve(rows);
                    }
                    connection.release(); //为每一个请求都建立一个connection使用完后调用connection.release(); 直接释放资源。
                                          //query用来操作数据库表
                })
            }
         
    })
    })
}

var users = `create table if not exists users(
    id INT(200) NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    pass VARCHAR(40) NOT NULL,
    avator VARCHAR(100) DEFAULT 'default.jpg', 
    job VARCHAR(40),
    company VARCHAR(40),
    introdu VARCHAR(255),
    userhome VARCHAR(100),
    github VARCHAR(100),
    PRIMARY KEY (id)
);`

let createTable = function(sql){
    return query(sql, []);
}

//建表
// createTable(users);

// 查询学生列表
let findStuList = function(obj){
    const { name, pageIndex, pageSize } = obj;
    // let _sql = `SELECT COUNT(id) AS NUMS FROM stuList_tb; SELECT * FROM stuList_tb where name like '%${name}%' limit ${(pageIndex-1)*pageSize},${pageSize}`
    // 把生日转为age
    let _sql = `SELECT COUNT(id) AS NUMS FROM stulist_tb;
                select 
                    id, name, sex, type, phone, notes,
                    DATE_FORMAT(birthday,"%Y-%m-%d") birthday,  
                    (year(now())-year(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) as age  
                from  
                    stulist_tb where name like '%${name}%' limit ${(pageIndex-1)*pageSize},${pageSize}`
    return query(_sql);
}

// 添加学生
let createStudent = function(value){
    let _sql = "insert into stulist_tb( name, sex, phone, type, birthday, notes, studentNo, portrait ) values(?,?,?,?,?,?,?,?);"
    return query(_sql,value);
}

// 查询菜单列表
let findMenuList = function(value){
    let _sql = "select * from menu_tb"
    return query(_sql,value);
}

// 登录
let login = function(obj){
    const { username, password } = obj;
    console.log(username)
    let _sql = `SELECT * FROM userinfo where username='${username}' and password = '${password}';`
                
    return query(_sql);
}


// 检验token
let checkToken = function() {
    let _sql = `select * from stuList_tb`
    return query(_sql);
}

module.exports = {
    findStuList,
    createStudent,
    findMenuList,
    login,
    checkToken
}